﻿using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace CPPEI.ETL.Infrastructure.Excel
{
    public static class OpenXmlTool
    {
        /// <summary>
        /// 读取表头信息
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="firstRowIsHeader"></param>
        /// <returns></returns>
        public static DataTable ReadExcelHeader(string filePath, bool firstRowIsHeader)
        {
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, false))
            {
                //打开excel
                IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>();
                if (sheets.Count() == 0)
                {
                    //找出合适前提的sheet,没有则返回
                    return null;
                }
                WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
                //获取Excel中共享数据
                SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
                IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();//获得Excel中得数据行
                DataTable dt = new DataTable("Excel");

                if (firstRowIsHeader)
                {
                    foreach (Row row in rows)
                    {
                        if (row.RowIndex == 1)
                        {
                            //Excel第一行动列名
                            SetColumnWithExcelColumnAndData(row, stringTable, ref dt);
                            break;
                        }
                    }
                }
                else
                {
                    foreach (Row row in rows)
                    {
                        if (row.RowIndex == 1)
                        {
                            //Excel第一行动列名
                            SetColumnWithExcelColumn(row, stringTable, ref dt);
                            break;
                        }
                    }
                }
                return dt;
            }
        }


        /// <summary>
        /// 读取第一个sheet页的数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="firstRowIsHeader"></param>
        /// <returns></returns>
        public static DataTable ReadExcel(string filePath, bool firstRowIsHeader)
        {
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, false))
            {
                //打开excel
                IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>();
                if (sheets.Count() == 0)
                {
                    //找出合适前提的sheet,没有则返回
                    return null;
                }
                WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
                //获取Excel中共享数据
                SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
                IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();//获得Excel中得数据行
                DataTable dt = new DataTable("Excel");

                if(firstRowIsHeader)
                {
                    foreach (Row row in rows)
                    {
                        if (row.RowIndex == 1)
                        {
                            //Excel第一行动列名
                            SetColumnWithExcelColumnAndData(row, stringTable, ref dt);
                        }
                        else
                        {
                            GetDataRow(row, stringTable, ref dt);
                        }
                    }
                }
                else
                {
                    
                    foreach (Row row in rows)
                    {
                        if (row.RowIndex == 1)
                        {
                            //Excel第一行动列名
                            SetColumnWithExcelColumn(row, stringTable, ref dt);
                        }
                        GetDataRow(row, stringTable, ref dt);
                    }
                }
                return dt;
            }
        }

        /// <summary>
        /// 按照给定的Excel流组织成Datatable
        /// </summary>
        /// <param name="filePath">Excel文件</param>
        /// <param name="sheetName">须要读取的Sheet</param>
        /// <returns>组织好的DataTable</returns>
        public static DataTable ReadExcel(string filePath, string sheetName, bool firstRowAsHeader)
        {
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, false))
            {
                //打开excel
                IEnumerable<Sheet> sheets = null;

                if(string.IsNullOrEmpty(sheetName))
                {
                    sheets = document.WorkbookPart.Workbook.Descendants<Sheet>();
                }
                else
                {
                    sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
                }
                if (sheets.Count() == 0)
                {
                    //找出合适前提的sheet,没有则返回
                    return null;
                }
                WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
                //获取Excel中共享数据
                SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
                IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();//获得Excel中得数据行
                DataTable dt = new DataTable("Excel");
                //因为须要将数据导入到DataTable中,所以我们假定Excel的第一行是列名,从第二行开端是行数据
                foreach (Row row in rows)
                {
                    if (row.RowIndex == 1)
                    {
                        if(firstRowAsHeader)
                        {
                            // 首行数据作为表头
                            SetColumnWithExcelColumnAndData(row, stringTable, ref dt);
                        }
                        else
                        {
                            SetColumnWithExcelColumn(row, stringTable, ref dt);
                            GetDataRow(row, stringTable, ref dt);
                        }
                    }
                    else
                    {
                        GetDataRow(row, stringTable, ref dt);
                    }
                }
                return dt;
            }
        }

        /// <summary>
        /// 根据excel的列名和数据的值来构建datatable的列
        /// </summary>
        /// <param name="row">OpenXML定义的Row对象</param>
        /// <param name="stringTablePart"></param>
        /// <param name="dt">须要返回的DataTable对象</param>
        /// <returns></returns>
        private static void SetColumnWithExcelColumnAndData(Row row, SharedStringTable stringTable, ref DataTable dt)
        {
            DataColumn col = new DataColumn();
            Dictionary<string, int> columnCount = new Dictionary<string, int>();
            foreach (Cell cell in row)
            {
                string cellVal = GetValue(cell, stringTable);
                string excelCol = GetExcelColName(cell.CellReference.Value, row.RowIndex.Value);

                string colName = string.Format("{0}:{1}", excelCol, cellVal);
                col = new DataColumn(colName);
                dt.Columns.Add(col);
            }
        }

        private static string GetExcelColName(string cellReference, uint rowIndex)
        {
            return cellReference.Replace(rowIndex.ToString(), string.Empty);
        }

        /// <summary>
        /// 根据excel的列生成datatable的列
        /// </summary>
        /// <param name="row"></param>
        /// <param name="stringTable"></param>
        /// <param name="dt"></param>
        private static void SetColumnWithExcelColumn(Row row, SharedStringTable stringTable, ref DataTable dt)
        {
            DataColumn col = new DataColumn();
            Dictionary<string, int> columnCount = new Dictionary<string, int>();
            foreach (Cell cell in row)
            {
                //string cellVal = GetValue(cell, stringTable);
                string excelCol = GetExcelColName(cell.CellReference.Value, row.RowIndex);

                string colName = string.Format("{0}:{1}", excelCol, excelCol);
                col = new DataColumn(colName);
                dt.Columns.Add(col);
            }
        }

        /// <summary>
        /// 构建DataTable的每一行数据,并返回该Datatable
        /// </summary>
        /// <param name="row">OpenXML的行</param>
        /// <param name="stringTablePart"></param>
        /// <param name="dt">DataTable</param>
        private static void GetDataRow(Row row, SharedStringTable stringTable, ref DataTable dt)
        {
            DataRow dr = dt.NewRow();

            foreach (Cell cell in row)
            {
                string excelCol = GetExcelColName(cell.CellReference.Value, row.RowIndex.Value);

                DataColumn existDtCol = null;
                // 获取对应的列
                foreach (DataColumn dtCol in dt.Columns)
                {
                    if (dtCol.ColumnName.StartsWith(string.Format("{0}:", excelCol)))
                    {
                        existDtCol = dtCol;
                        break;
                    }
                }

                if (existDtCol != null)
                {
                    string cellVal = GetValue(cell, stringTable);
                    dr[existDtCol.ColumnName] = cellVal;
                }
            }
            dt.Rows.Add(dr);
        }

        /// <summary>
        /// 获取单位格的值
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="stringTablePart"></param>
        /// <returns></returns>
        private static string GetValue(Cell cell, SharedStringTable stringTable)
        {
            //因为Excel的数据存储在SharedStringTable中,须要获取数据在SharedStringTable 中的索引
            string value = string.Empty;
            try
            {
                if (cell.ChildElements.Count == 0)
                    return value;
                value = double.Parse(cell.CellValue.InnerText).ToString();
                if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
                {
                    value = stringTable.ChildElements[Int32.Parse(value)].InnerText;
                }
            }
            catch (Exception)
            {
                value = "N/A";
            }
            return value;
        }

        /// <summary>
        /// 判断网格是否存在列
        /// </summary>
        /// <param name="dt">网格</param>
        /// <param name="columnName">列名</param>
        /// <returns></returns>
        public static bool IsContainsColumn(DataTable dt, string columnName)
        {
            if (dt == null || columnName == null)
            {
                return false;
            }
            return dt.Columns.Contains(columnName);
        }
    }
}
